package com.jxc.dao.impl;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import jxl.Workbook;
import jxl.write.WritableFont;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.jxc.dao.CommonUtilDao;
import com.jxc.entity.TbEmployee;

@Transactional
public class CommonUtilDaoImpl implements CommonUtilDao {
	@Resource private SessionFactory sessionFactory;
	private static final Log log = LogFactory.getLog(CommonUtilDaoImpl.class);

	/* (non-Javadoc)
	 * @see com.jxc.dao.impl.CommonUtilDao#QueryList(java.lang.String, java.lang.Integer, java.lang.Integer)
	 */
	@Transactional(propagation=Propagation.NOT_SUPPORTED,readOnly=true)
	
	public List QueryList(String HQL,Integer first,Integer max ){
		List list= null;
		log.debug("finding all RoleEmployee instances");
		try {
			list  = sessionFactory.getCurrentSession().createQuery(HQL).setFirstResult(first).setMaxResults(max).list();
			return list;
		} catch (RuntimeException re) {
			//log.error("find all failed", re);
			throw re;
		}
	}
	
	/* (non-Javadoc)
	 * @see com.jxc.dao.impl.CommonUtilDao#QueryList(java.lang.String)
	 */
	@Transactional(propagation=Propagation.NOT_SUPPORTED,readOnly=true)
	public List QueryList(String HQL ){
		List list= null;
		log.debug("finding all RoleEmployee instances");
		try {
			list  = sessionFactory.getCurrentSession().createQuery(HQL).list();
			return list;
		} catch (RuntimeException re) {
			//log.error("find all failed", re);
			throw re;
		}
	}
	/* (non-Javadoc)
	 * @see com.jxc.dao.impl.CommonUtilDao#QuerySize(java.lang.String)
	 */
	@Transactional(propagation=Propagation.NOT_SUPPORTED,readOnly=true)
	public Integer QuerySize(String HQL ){
		List list= null;
		log.debug("finding all RoleEmployee instances");
		try {
			list  = sessionFactory.getCurrentSession().createQuery(HQL).list();
			return list!=null?list.size():0;
		} catch (RuntimeException re) {
			//log.error("find all failed", re);
			throw re;
		}
	}

	@Transactional(propagation=Propagation.NOT_SUPPORTED,readOnly=true)
	public List QueryNativeList(String HQL) {
		List list= null;
		log.debug("finding all RoleEmployee instances");
		try {
			list  = sessionFactory.getCurrentSession().createSQLQuery(HQL).list();
			return list;
		} catch (RuntimeException re) {
			//log.error("find all failed", re);
			throw re;
		}
	}

	@Transactional(propagation=Propagation.NOT_SUPPORTED,readOnly=true)
	public List QueryNativeList(String HQL, Integer first, Integer max) {
		List list= null;
		log.debug("finding all RoleEmployee instances");
		try {
			list  = sessionFactory.getCurrentSession().createSQLQuery(HQL).setFirstResult(first).setMaxResults(max).list();
			return list;
		} catch (RuntimeException re) {
			//log.error("find all failed", re);
			throw re;
		}
	}

	@Transactional(propagation=Propagation.NOT_SUPPORTED,readOnly=true)
	public List QueryNativeListEntity(String entity,String HQL, Integer first, Integer max) {
		List list= null;
		log.debug("finding all RoleEmployee instances");
		try {
			list  = sessionFactory.getCurrentSession().createSQLQuery(HQL).addEntity(entity).setFirstResult(first).setMaxResults(max).list();
			return list;
		} catch (RuntimeException re) {
//			log.error("find all failed", re);
			throw re;
		}
	}
	
	@Transactional(propagation=Propagation.NOT_SUPPORTED,readOnly=true)
	public Integer QueryNativeSize(String HQL) {
		List list= null;
		log.debug("finding all RoleEmployee instances");
		try {
			list  = sessionFactory.getCurrentSession().createSQLQuery(HQL).list();
			return list!=null?list.size():0;
		} catch (RuntimeException re) {
//			log.error("find all failed", re);
			throw re;
		}
		
	}
	@Transactional(propagation=Propagation.NOT_SUPPORTED,readOnly=true)
	public String QueryNativeFile(String HQL) {
		String str2="";
		log.debug("finding all RoleEmployee instances");
		try {
				Query query  = sessionFactory.getCurrentSession().createSQLQuery(HQL).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				if(query.list().size()>0){
				Map map = (Map)query.list().get(0); 
				Set str=map.keySet();
				 Iterator iter = str.iterator();
				 int i=0;
				 while(iter.hasNext()){
					  i++;
					 str2+=(String) iter.next();
					if (i != str.size()) {
						str2 += ",";
					}
				 }
				}
				log.info("返回字段名称    "+str2+"            ");
				return str2;
		} catch (RuntimeException re) {
			//log.error("find all failed", re);
			throw re;
		}
		
	}
	
	@Transactional(propagation=Propagation.NOT_SUPPORTED,readOnly=true)
	public String QueryFile(String HQL) {
		String str2="";
		List list =null;
		log.debug("finding all RoleEmployee instances");
		try {
				Query query  = sessionFactory.getCurrentSession().createQuery(HQL).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				list=query.list();
				if(query.list().size()>0){
				Map map = (Map)query.list().get(0); 
				
				Set str=map.keySet();
				 Iterator iter = str.iterator();
				 int i=0;
				 while(iter.hasNext()){
					  i++;
					 str2+=(String) iter.next();
					if (i != str.size()) {
						str2 += ",";
					}
				 }
				}
				
				log.info("返回字段名称    "+str2+"            ");
				return str2;
		} catch (RuntimeException re) {
		//	log.error("find all failed", re);
			throw re;
		}
	}
	
	@Transactional(propagation=Propagation.NOT_SUPPORTED,readOnly=true)
	public InputStream QueryFileInputStream(String HQL) {
		String str2="";
		List list =null;
		InputStream excelStream=null ;
		log.debug("finding all RoleEmployee instances");
		try {
				Query query  = sessionFactory.getCurrentSession().createQuery(HQL).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				list=query.list();
				if(query.list().size()>0){
				Map map = (Map)query.list().get(0); 
				Set str=map.keySet();
				 Iterator iter = str.iterator();
				 int i=0;
				 while(iter.hasNext()){
					  i++;
					 str2+=(String) iter.next();
					if (i != str.size()) {
						str2 += ",";
					}
				 }
				}
				File f= new File("");
				ByteArrayOutputStream out = new ByteArrayOutputStream();
				SQLwriteExcel(out,list,str2,"工单报表");
				excelStream = new ByteArrayInputStream(out.toByteArray());
				log.info("返回字段名称    "+str2+"            ");
		} catch (RuntimeException re) {
			//log.error("find all failed", re);
			throw re;
		} catch (FileNotFoundException e) {
			//e.printStackTrace();
		} catch (Exception e) {
			//e.printStackTrace();
		}
		return excelStream;
	}
	
	@Transactional(propagation=Propagation.NOT_SUPPORTED,readOnly=true)
	public InputStream QueryFileInputStreamNative(String HQL) {
		String str2="";
		List list =null;
		InputStream excelStream=null ;
		log.debug("finding all RoleEmployee instances");
		try {
				Query query  = sessionFactory.getCurrentSession().createSQLQuery(HQL).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				list=query.list();
				if(query.list().size()>0){
				Map map = (Map)query.list().get(0); 
				Set str=map.keySet();
				 Iterator iter = str.iterator();
				 int i=0;
				 while(iter.hasNext()){
					  i++;
					 str2+=(String) iter.next();
					if (i != str.size()) {
						str2 += ",";
					}
				 }
				}
				File f= new File("");
				ByteArrayOutputStream out = new ByteArrayOutputStream();
				SQLwriteExcel(out,list,str2,"工单报表");
				excelStream = new ByteArrayInputStream(out.toByteArray());
				log.info("返回字段名称    "+str2+"            ");
		} catch (RuntimeException re) {
			//log.error("find all failed", re);
			throw re;
		} catch (FileNotFoundException e) {
			//e.printStackTrace();
		} catch (Exception e1) {
			//e.printStackTrace();
		}
		return excelStream;
	}
	
	//更新用的sql
	public int Update_Native(TbEmployee e) {
		log.debug("finding all RoleEmployee instances");
		String sql="";
		try {
//			String sql="update tb_employee set name='"+e.getName()+"',sex='"+e.getSex()+"' where id="+e.getId();
			 sql="update tb_employee set name='AAAAAA' where id=2";
			sessionFactory.getCurrentSession().createSQLQuery(sql).executeUpdate();
			return 1;
		} catch (RuntimeException re) {
			//log.error("find all failed", re);
			throw re;
		}
	}
	
	
	public String[]  select(String sql){
		String[] array=null;
		Connection conn =null;
		PreparedStatement pst = null;
		Statement stmt = null;
		ResultSet rs = null;
//		Connection
		 try {
			  stmt = sessionFactory.openSession().connection().createStatement();
			  rs = stmt.executeQuery(sql);
		      ResultSetMetaData rsmd = rs.getMetaData();
		      int  tmpColumnCount = rsmd.getColumnCount();
		      log.info("长度："+tmpColumnCount);
		      for (int i = 1; i <= tmpColumnCount; i++) {
		    	  String colName=rsmd.getColumnName(i); //获取字段名 (全大写)     
		    	  log.info("字段"+colName);
		      }
		} catch (SQLException e) {
			//e.printStackTrace();
		}
		return array;
		}

	
	
	public static void  SQLwriteExcel(OutputStream os, List list, String str,
			 String labelName) throws Exception {
		String [] str2 =str.split(",");
		jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);
		jxl.write.WritableSheet ws = wwb.createSheet("sheet1", 0);
		jxl.write.Label labelC = null;
		jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(
				WritableFont.ARIAL, 20, WritableFont.BOLD, false,
				jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
		jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(
				wfc1);
		// wcfFC1.setBackground(jxl.format.Colour.BLUE);
		labelC = new jxl.write.Label(6, 0, labelName, wcfFC);
		ws.addCell(labelC);
		for (int i = 0; i < str2.length; i++) {
			labelC = new jxl.write.Label(i + 1, 1, str2[i]);
			ws.addCell(labelC);
		}
		//Object[] t = null;
		for (int i = 0; i < list.size(); i++) {
			Map map=(Map) list.get(i);
			//t = (Object[]) list.get(i);
			int x = str2.length;
//			log.info("x 长度为："+x);
			for (int j = 1; j < x + 1; j++) {
				if (map.get(str2[j-1]) != null) {
					labelC = new jxl.write.Label(j, i + 2, map.get(str2[j-1]).toString());
					ws.addCell(labelC);
				} else {
					labelC = new jxl.write.Label(j, i + 2, "");
					ws.addCell(labelC);
				}
			}
		}
		
		wwb.write();
		wwb.close();
	}
	
	//分页——根据工单创建人公司地市名查找工单详细信息
	//增加条件：开始时间，结束时间
	public List findtask_bycityname(String cityname,int start, int limit,String startime,String endtime) {
		try {
			//---------------------------工单ID--------工单状态------客户机型号--------客户姓名--------------------------------------------------------客户级别-------------------------------------------------------故障描述--------维修网点------------创建时间------------------------------------------------
			String queryString = "select t.id,b.keyValue as zt,t.tbMobile.model,t.customerName,(select a.keyValue from TbDict as a where a.keyGroup='tb_customer_class' and a.keyId=t.customerClass) as khjb,t.faultDesc,t.tbCompanyRep.name,t.createTime from TbTask as t,TbDict as b where t.tbEmployee.tbCompany.tbCity.cityName='"+cityname+"' and b.keyGroup='tb_task_zt' and b.keyId=t.currentStatus";
			if(!startime.equals("")){
				queryString+=" and t.createTime>='"+startime+"'";
			}
			if(!endtime.equals("")){
				queryString+=" and t.createTime<='"+endtime+"'";
			}
			queryString+=" order by t.id";
			Query queryObject = sessionFactory.getCurrentSession().createQuery(queryString);
			queryObject.setFirstResult(start);
			queryObject.setMaxResults(limit);
			return queryObject.list();
		} catch (RuntimeException re) {
			re.printStackTrace();
			throw re;
		}
	}
	//总数——根据工单创建人公司地市名查找工单详细信息
	//增加条件：开始时间，结束时间
	public int findtask_bycityname_totalRecord(String cityname,String startime,String endtime) {
		int count = 0;
		try {
			String queryString = "select count(t.id) from TbTask t where t.tbEmployee.tbCompany.tbCity.cityName='"+cityname+"'";
			if(!startime.equals("")){
				queryString+=" and t.createTime>='"+startime+"'";
			}
			if(!endtime.equals("")){
				queryString+=" and t.createTime<='"+endtime+"'";
			}
			queryString+=" order by t.id";
			Query queryObject = sessionFactory.getCurrentSession().createQuery(
					queryString);
			count = ((Long) queryObject.uniqueResult()).intValue();
		} catch (RuntimeException re) {
			throw re;
		}
		return count;
	}

	//分页——根据故障型号、故障类型类型查找故障现象
	//增加条件：开始时间，结束时间
	public List findfault_bycityname(String type,String model,int start, int limit,String startime,String endtime) {
		try {
			//-------------------------------------------品牌------------------------------型号-------------故障类型-------------故障现象-------------工单ID------------------
			String queryString = "select t.tbTask.tbMobile.tbCompanyMobile.brand,t.tbTask.tbMobile.model,b.keyValue,t.tbFaultBase.faultDetail,t.tbTask.id from TbTaskFault as t,TbDict as b where t.tbFaultBase.type="+type+" and t.tbTask.tbMobile.model='"+model+"' and b.keyGroup='tb_fault_base_type' and b.keyId=t.tbFaultBase.type";
			if(!startime.equals("")){
				queryString+=" and t.tbTask.createTime>='"+startime+"'";
			}
			if(!endtime.equals("")){
				queryString+=" and t.tbTask.createTime<='"+endtime+"'";
			}
			queryString+=" order by t.tbTask.id desc";
			Query queryObject = sessionFactory.getCurrentSession().createQuery(queryString);
			queryObject.setFirstResult(start);
			queryObject.setMaxResults(limit);
			return queryObject.list();
		} catch (RuntimeException re) {
			re.printStackTrace();
			throw re;
		}
	}
	//总数——根据故障型号、故障类型类型查找故障现象
	//增加条件：开始时间，结束时间
	public int findfault_bycityname_totalRecord(String type,String model,String startime,String endtime) {
		int count = 0;
		try {
			String queryString = "select count(t.id) from TbTaskFault t where t.tbFaultBase.type="+type+" and t.tbTask.tbMobile.model='"+model+"'";
			if(!startime.equals("")){
				queryString+=" and t.tbTask.createTime>='"+startime+"'";
			}
			if(!endtime.equals("")){
				queryString+=" and t.tbTask.createTime<='"+endtime+"'";
			}
			queryString+=" order by t.id desc";
			Query queryObject = sessionFactory.getCurrentSession().createQuery(
					queryString);
			count = ((Long) queryObject.uniqueResult()).intValue();
		} catch (RuntimeException re) {
			throw re;
		}
		return count;
	}
	
	@Transactional(propagation=Propagation.NOT_SUPPORTED,readOnly=true)
	public int GetId(String SQL) {
		log.debug("finding all RoleEmployee instances");
		try {
			Integer id=(Integer) sessionFactory.getCurrentSession().createSQLQuery(SQL).uniqueResult();
			return (int) (id==null?0:id);
		} catch (RuntimeException re) {
			//log.error("find all failed", re);
			throw re;
		}
	}
	@Transactional(propagation=Propagation.NOT_SUPPORTED,readOnly=true)
	public Integer GetCount(String SQL) {
		log.debug("finding all RoleEmployee instances");
		try {
			Query queryObject=sessionFactory.getCurrentSession().createSQLQuery(SQL);
			int count = ((Long) queryObject.uniqueResult()).intValue();
			return count;
		} catch (RuntimeException re) {
			//log.error("find all failed", re);
			throw re;
		}
	}
}